﻿/**  版本信息模板在安装目录下，可自行修改。
* Barcode.cs
*
* 功 能： N/A
* 类 名： Barcode
*
* Ver    变更日期             负责人  变更内容
* ───────────────────────────────────
* V0.01  2013-5-3 14:17:34   N/A    初版
*
* Copyright (c) 2012 Maticsoft Corporation. All rights reserved.
*┌──────────────────────────────────┐
*│　此技术信息为本公司机密信息，未经本公司书面同意禁止向第三方披露．　│
*│　版权所有：动软卓越（北京）科技有限公司　　　　　　　　　　　　　　│
*└──────────────────────────────────┘
*/
using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace DMZS.DAL
{
    /// <summary>
    /// 数据访问类:Barcode
    /// </summary>
    public partial class Barcode
    {
        public Barcode()
        { }
        #region  BasicMethod

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(long ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Barcode");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.BigInt)
			};
            parameters[0].Value = ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public long Add(DMZS.Model.Barcode model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Barcode(");
            strSql.Append("Code,ProductID,CreateTime,Status,PrintCount,ReturnCount,ParentID)");
            strSql.Append(" values (");
            strSql.Append("@Code,@ProductID,@CreateTime,@Status,@PrintCount,@ReturnCount,@ParentID)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Code", SqlDbType.NVarChar,100),
					new SqlParameter("@ProductID", SqlDbType.BigInt,8),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@PrintCount", SqlDbType.Int,4),
					new SqlParameter("@ReturnCount", SqlDbType.Int,4),
					new SqlParameter("@ParentID", SqlDbType.BigInt,8)};
            parameters[0].Value = model.Code;
            parameters[1].Value = model.ProductID;
            parameters[2].Value = model.CreateTime;
            parameters[3].Value = model.Status;
            parameters[4].Value = model.PrintCount;
            parameters[5].Value = model.ReturnCount;
            parameters[6].Value = model.ParentID;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt64(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(DMZS.Model.Barcode model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Barcode set ");
            strSql.Append("Code=@Code,");
            strSql.Append("ProductID=@ProductID,");
            strSql.Append("CreateTime=@CreateTime,");
            strSql.Append("Status=@Status,");
            strSql.Append("PrintCount=@PrintCount,");
            strSql.Append("ReturnCount=@ReturnCount,");
            strSql.Append("ParentID=@ParentID");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@Code", SqlDbType.NVarChar,100),
					new SqlParameter("@ProductID", SqlDbType.BigInt,8),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@PrintCount", SqlDbType.Int,4),
					new SqlParameter("@ReturnCount", SqlDbType.Int,4),
					new SqlParameter("@ParentID", SqlDbType.BigInt,8),
					new SqlParameter("@ID", SqlDbType.BigInt,8)};
            parameters[0].Value = model.Code;
            parameters[1].Value = model.ProductID;
            parameters[2].Value = model.CreateTime;
            parameters[3].Value = model.Status;
            parameters[4].Value = model.PrintCount;
            parameters[5].Value = model.ReturnCount;
            parameters[6].Value = model.ParentID;
            parameters[7].Value = model.ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(long ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Barcode ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.BigInt)
			};
            parameters[0].Value = ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Barcode ");
            strSql.Append(" where ID in (" + IDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DMZS.Model.Barcode GetModel(long ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,Code,ProductID,CreateTime,Status,PrintCount,ReturnCount,ParentID from Barcode ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.BigInt)
			};
            parameters[0].Value = ID;

            DMZS.Model.Barcode model = new DMZS.Model.Barcode();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DMZS.Model.Barcode DataRowToModel(DataRow row)
        {
            DMZS.Model.Barcode model = new DMZS.Model.Barcode();
            if (row != null)
            {
                if (row["ID"] != null && row["ID"].ToString() != "")
                {
                    model.ID = long.Parse(row["ID"].ToString());
                }
                if (row["Code"] != null)
                {
                    model.Code = row["Code"].ToString();
                }
                if (row["ProductID"] != null && row["ProductID"].ToString() != "")
                {
                    model.ProductID = long.Parse(row["ProductID"].ToString());
                }
                if (row["CreateTime"] != null && row["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(row["CreateTime"].ToString());
                }
                if (row["Status"] != null && row["Status"].ToString() != "")
                {
                    model.Status = int.Parse(row["Status"].ToString());
                }
                if (row["PrintCount"] != null && row["PrintCount"].ToString() != "")
                {
                    model.PrintCount = int.Parse(row["PrintCount"].ToString());
                }
                if (row["ReturnCount"] != null && row["ReturnCount"].ToString() != "")
                {
                    model.ReturnCount = int.Parse(row["ReturnCount"].ToString());
                }
                if (row["ParentID"] != null && row["ParentID"].ToString() != "")
                {
                    model.ParentID = long.Parse(row["ParentID"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Code,ProductID,CreateTime,Status,PrintCount,ReturnCount,ParentID ");
            strSql.Append(" FROM Barcode ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ID,Code,ProductID,CreateTime,Status,PrintCount,ReturnCount,ParentID ");
            strSql.Append(" FROM Barcode ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Barcode ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.ID desc");
            }
            strSql.Append(")AS Row, T.*  from Barcode T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "Barcode";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod


        /// <summary>
        /// 添加扩展
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public long AddExpand(DMZS.Model.Barcode model)
        {
            string strSql = @"
if exists (select 1 from Barcode where Code=@Code) begin
    select 0
    return
end
insert into Barcode(Code,ProductID,CreateTime,Status,PrintCount,ReturnCount,ParentID)
values (@Code,@ProductID,@CreateTime,@Status,@PrintCount,@ReturnCount,@ParentID)

declare @BarcodeID bigint
select @BarcodeID=@@IDENTITY
insert into BarcodeOperationLog(BarcodeID,NewStatus,OldStatus,Time,OrderTypeID,OrderID,OrderLineID)
values(@BarcodeID,0,-1,getdate(),0,0,0)

select @BarcodeID";
            SqlParameter[] parameters = {
					new SqlParameter("@Code", SqlDbType.NVarChar,100),
					new SqlParameter("@ProductID", SqlDbType.BigInt,8),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@PrintCount", SqlDbType.Int,4),
					new SqlParameter("@ReturnCount", SqlDbType.Int,4),
					new SqlParameter("@ParentID", SqlDbType.BigInt,8) };
            parameters[0].Value = model.Code;
            parameters[1].Value = model.ProductID;
            parameters[2].Value = model.CreateTime;
            parameters[3].Value = model.Status;
            parameters[4].Value = model.PrintCount;
            parameters[5].Value = model.ReturnCount;
            parameters[6].Value = model.ParentID;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt64(obj);
            }
        }

        /// <summary>
        /// 更新打印次数
        /// </summary>
        /// <param name="Code"></param>
        /// <returns></returns>
        public long UpdatePrintCount(string Code)
        {
            string strSql = @"
declare @id bigint
declare @printCount int
declare @status int
select @id=ID,@printCount=isnull(PrintCount,0),@status=Status from Barcode where Code=@Code 

if @id is null begin
    select 1
    return
end

if @printCount=0 and @status=0 begin
    update Barcode set Status=1,PrintCount=1 where Code=@Code 
    select 1
end else begin
    update Barcode set PrintCount=isnull(PrintCount,0)+1 where Code=@Code 
    select 1    
end

insert into BarcodeOperationLog(BarcodeID,NewStatus,OldStatus,Time,OrderTypeID,OrderID,OrderLineID)
values(@id,-1,@status,getdate(),0,0,0)
";
            SqlParameter[] parameters = {
					new SqlParameter("@Code", SqlDbType.NVarChar,100)};
            parameters[0].Value = Code;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt64(obj);
            }
        }
        /// <summary>
        /// 获取记录行
        /// </summary>
        /// <param name="Code"></param>
        /// <returns></returns>
        public DataRow GetRecordByCode(string Code)
        {
            string strSql = @"
select top 1
    b.ID,b.Code,b.ProductID,b.CreateTime,b.Status,b.PrintCount,b.ReturnCount,b.ParentID,
    p.Code ProductCode,p.Name ProductName,
    u.UnitBaseID,u.BaseCode UnitBaseCode,u.BaseName UnitBaseName
from Barcode b
join Product p on p.ID=b.ProductID
left join UnitBase u on u.UnitBaseID=p.UnitID
where b.Code=@Code";
            SqlParameter[] parameters = {
					new SqlParameter("@Code", SqlDbType.NVarChar,100)};
            parameters[0].Value = Code;
            DataSet ds = DbHelperSQL.Query(strSql, parameters);
            if (ds != null && ds.Tables[0].Rows.Count > 0) return ds.Tables[0].Rows[0];
            else return null;
        }

        /// <summary>
        /// 是否在订单中
        /// </summary>
        /// <param name="BarcodeID"></param>
        /// <param name="OrderID"></param>
        /// <param name="OrderTypeID"></param>
        /// <returns></returns>
        public bool IfInOrder(long BarcodeID, long OrderID, int OrderTypeID)
        {
            string strSql = "select count(1) from BarcodeStorageOperation where BarcodeID=@BarcodeID and OrderID=@OrderID and OrderTypeID=@OrderTypeID";
            SqlParameter[] parameters = {
					new SqlParameter("@BarcodeID", SqlDbType.BigInt),
					new SqlParameter("@OrderID", SqlDbType.BigInt),
					new SqlParameter("@OrderTypeID", SqlDbType.Int)
			};

            parameters[0].Value = BarcodeID;
            parameters[1].Value = OrderID;
            parameters[2].Value = OrderTypeID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public DataSet GetBarocdeTarce(DateTime Date_begin, DateTime Date_end, string barocde_begin, string barcode_end, string barcodeStatus, string productCode, string number_rkd, string number_ckd)
        {
            string strSql = @"select * from vBarcodeTrace where 1=1 ";
            string strCondition_date = " and convert(nvarchar,CreateTime,112) between convert(nvarchar,@Date_begin,112) and convert(nvarchar,@Date_end,112) ";
            string strCondition_barcode = string.Empty;
            if (barocde_begin != "" && barcode_end != "")
            {
                strCondition_barcode = " and Code between @Barcode_begin and @Barcode_end ";
            }
            else if (barocde_begin == "" && barcode_end != "")
            {
                strCondition_barcode = " and Code <= @Barcode_end ";
            }
            else if (barocde_begin != "" && barcode_end == "")
            {
                strCondition_barcode = " and Code >= @Barcode_begin ";
            }
            string strConditionStatus = string.Empty;
            switch (barcodeStatus)
            {
                //case "全部":
                //    strConditionStatus = string.Empty;
                //    break;
                case "入库待审":
                    strConditionStatus = " and status=10 ";
                    break;
                case "入库已审":
                    strConditionStatus = " and status= 11 ";
                    break;
                case "出库待审":
                    strConditionStatus = " and status=20 ";
                    break;
                case "出库已审":
                    strConditionStatus = " and status=21 ";
                    break;
                case "已打印":
                    strConditionStatus = " and PrintCount>0 ";
                    break;
                case "未打印":
                    strConditionStatus = " and PrintCount=0 ";
                    break;
            }
            string strCondition_productcode = " and ProductCode like '%'+@ProductCode+'%' ";
            string strCondition_number_rkd = " and ProductCode like '%'+@Number_rkd+'%' ";
            string strCondition_number_ckd = " and ProductCode like '%'+@Number_ckd+'%' ";

            strSql += strCondition_date;
            strSql += strCondition_barcode;
            strSql += strCondition_productcode;
            strSql += strConditionStatus;
            strSql += strCondition_productcode;
            strSql += strCondition_number_rkd;
            strSql += strCondition_number_ckd;
            SqlParameter[] parameters ={
                new SqlParameter("@Date_begin",SqlDbType.DateTime),
                new SqlParameter("@Date_end",SqlDbType.DateTime),
                new SqlParameter("@Barcode_begin",SqlDbType.NVarChar,100),
                new SqlParameter("@Barcode_end",SqlDbType.NVarChar,100),
                new SqlParameter("@ProductCode",SqlDbType.NVarChar,50),
                new SqlParameter("@Number_rkd",SqlDbType.NVarChar,50),
                new SqlParameter("@Number_ckd",SqlDbType.NVarChar,50) };
            parameters[0].Value = Date_begin;
            parameters[1].Value = Date_end;
            parameters[2].Value = barocde_begin;
            parameters[3].Value = barcode_end;
            parameters[4].Value = productCode;
            parameters[5].Value = number_rkd;
            parameters[6].Value = number_ckd;

            return DbHelperSQL.Query(strSql, parameters);
        }
    }
}

